import asyncio
import logging
import mysql.connector
import hashlib


class ForSql(object):
    def __init__(self, host, port, user, passwd, database, table):
        """
        :param user: string, mysql username
        :param passwd: string, mysql password
        :param database: string, mysql database's name
        """
        self.table = table
        self.database = database
        self.conn = mysql.connector.connect(host=host, port=port, user=user, password=passwd, database=self.database)

    def check_exist(self, url):
        """
        检查一个url是否被存过，如果是，则近回True,否则返回False
        Args:
            url: string
        Return:
            True or False
        """
        cursor = self.conn.cursor()
        execute = 'select * from {} where url = %s'.format(self.table)
        cursor.execute(execute, (url,))
        if cursor.fetchall():
            cursor.close()
            return True
        else:
            cursor.close()
            return False

    # def insert_date(self, url, title, date,  keys):
    #     if self.check_exist(url):
    #         return
    #     cursor = self.conn.cursor()
    #     if len(keys) < 6:
    #         pass
    #     else:
    #         keys = keys[:11]
    #     data = [url,title,date]
    #     data.extend(keys)
    #     # print(data)
    #     execute = 'insert into url_date_keywords('
    #     execute += 'url,title,date'
    #     if 3 < len(data):
    #         for i in range(len(data)-3):
    #             execute += ',key{}'.format(i+1)
    #     execute += ') '
    #
    #     execute += 'values(%s'
    #     for i in data[:-1]:
    #         execute += ', %s'
    #     execute += ')'
    #     # print(execute)
    #     cursor.execute(execute, data)
    #     self.conn.commit()
    #     cursor.close()
    #     return

    def get_date(self, keywords, limit_range=0):
        cursor = self.conn.cursor()
        r = []
        # print(keywords)
        for k in keywords:
            try:
                execute = "select * from {} where key1='{}'".format(self.table, k)

                execute += "order by date desc limit 0,{};".format(limit_range + 10)
                print(execute)
                cursor.execute(execute)
                r.extend(cursor.fetchall())

            except mysql.connector.errors.InterfaceError:
                print("没有{}这个关键字".format(k))
        return r

    def insert_date(self, url, title, date, keys):
        if self.check_exist(url):
            return
        cursor = self.conn.cursor()
        # if len(keys) < 6:
        #     pass
        # else:
        #     keys = keys[:11]
        for k in keys:
            execute = 'insert into {}(url, title, date, key1) VALUES (%s, %s, %s, %s)'.format(self.table)
            # print(execute)
            cursor.execute(execute, (url, title, date, k,))
        self.conn.commit()
        cursor.close()
        return

    def __del__(self):
        if self.conn:
            self.conn.close()


class User_ForSql(ForSql):
    def verify_user(self, name, password):
        execute = "select * from {} where name='{}'".format(self.table, name)
        cursor = self.conn.cursor()
        cursor.execute(execute)
        f = cursor.fetchall()
        password_md5 = hashlib.md5()
        password_md5.update(str.encode(password))
        if password_md5.hexdigest() == f[0][1]:
            return True
        else:
            return False

    def check_exist(self, name):
        """
        检查一个 name 是否被存过，如果是，则近回True,否则返回False
        Args:
            name: string
        Return:
            True or False
        """
        cursor = self.conn.cursor()
        execute = 'select * from {} where name = %s'.format(self.table)
        cursor.execute(execute, (name,))

        if cursor.fetchall():
            cursor.close()
            return True
        else:
            cursor.close()
            return False

    def insert_date(self, name, password):
        if self.check_exist(name):
            return
        cursor = self.conn.cursor()

        execute = 'insert into {}(name,password_md5) VALUES (%s, %s)'.format(self.table)
        password_md5 = hashlib.md5()
        password_md5.update(str.encode(password))
        cursor.execute(execute, (name, password_md5.hexdigest()))
        self.conn.commit()
        cursor.close()
        return

    def update_date(self, name, password):
        if not self.check_exist(name):
            return
        cursor = self.conn.cursor()

        execute = 'update {} set password_md5=%s where name=%s'.format(self.table)
        password_md5 = hashlib.md5()
        password_md5.update(str.encode(password))
        cursor.execute(execute, (password_md5.hexdigest(),name))
        self.conn.commit()
        cursor.close()
        return


class User_Follower_ForSql(ForSql):
    def get_follower(self, user_name):
        execute = "select * from {} where user_name='{}'".format(self.table, user_name)
        cursor = self.conn.cursor()
        cursor.execute(execute)
        folloers = []
        for i in cursor.fetchall():
            folloers.append(i[1])
        return set(folloers)

    def insert_new_follower(self, user_name, follower_str):
        execute = "insert into user_subscription VALUES (%s,%s)"
        cursor = self.conn.cursor()
        cursor.execute(execute, (user_name, follower_str,))
        self.conn.commit()
        cursor.close()
        return

    def del_follower(self, user_name, follower_str):
        execute = "DELETE FROM user_subscription WHERE user_name=%s and user_subscription=%s"
        # execute = "insert into user_subscription VALUES (%s,%s)"
        cursor = self.conn.cursor()
        cursor.execute(execute, (user_name, follower_str,))
        self.conn.commit()
        cursor.close()
        return